import pandas as pd
import numpy as np
import math
from matplotlib import pyplot as plt


# 提取数字，处理身高体重信息
def get_num(str):
    numbers = []
    for char in str:
        if char.isdigit():
            numbers.append(char)
    num = 0
    for i in range((len(numbers))):
        # 保留一位小数
        num = round(float(numbers[len(numbers) - i - 1]) * 10 ** i + num, 1)
    return num


# 1
df1 = pd.read_excel("数据2.xlsx")
df2 = pd.read_csv("数据1.csv", encoding='gbk')
merged_data = pd.concat([df1, df2], axis=0)
print("合并后数据前五行为：")
print(merged_data.head())
# 2
print("筛选后的数据为：")
CN_female_bas = merged_data[
    (merged_data['国籍'] == '中国') & (merged_data['项目'] == '篮球') & (merged_data['性别'] == '女')]
print(CN_female_bas.head())
# 3
# 3.1
# 去除名字中的空格
CN_female_bas.loc[:, '中文名'] = CN_female_bas['中文名'].str.strip()
CN_female_bas.loc[:, '外文名'] = CN_female_bas['外文名'].str.strip()
# 去除重复值
processed_data = CN_female_bas.drop_duplicates(subset=['中文名'])
# 3.2
# (1)
# 数据中身高的数据格式不统一，且数字与汉字并存，所以要先处理数据
temp = processed_data.loc[:, '身高'].array
# 遍历数组

for i in range(len(temp)):
    if pd.isna(temp[i]):
        continue
    else:
        temp[i] = get_num(temp[i])
processed_data.loc[:, '身高'] = temp[:]
processed_data['身高'].fillna(round(processed_data['身高'].mean(), 1), inplace=True)
processed_data.columns = ['中文名', '外文名', '性别', '国籍', '身高/cm', '体重', '项目', '省份']
# (2)
temp = processed_data.loc[:, '体重'].array
for i in range(len(temp)):
    if pd.isna(temp[i]):
        continue
    else:
        temp[i] = get_num(temp[i])
# 利用正态分布的3σ原则处理异常值
mean = temp.mean()
std = temp.std()
for i in range(len(temp)):
    if abs(temp[i] - mean) > std * 3:
        temp[i] = np.nan
# 剔除异常值后重新计算平均值
new_mean = temp.mean()
for i in range(len(temp)):
    if np.isnan(temp[i]):
        temp[i] = round(new_mean, 1)
for i in range(len(temp)):
    temp[i] = str(temp[i]) + 'kg'
processed_data.loc[:, '体重'] = temp[:]
processed_data['省份'].fillna('未知', inplace=True)
processed_data.loc[processed_data['中文名'] == '陈楠', '省份'] = '青岛'
processed_data.iloc[processed_data['中文名'] == '陈晓佳', np.where(processed_data.columns == '省份')[0]] = '江苏'
# 4
player_height_median = processed_data['身高/cm'].median()
player_height_mode = processed_data['身高/cm'].mode()
player_height_std = processed_data['身高/cm'].std()
print(f'女篮运动员身高的\n中位数：{player_height_median}\n众数：{player_height_mode}\n标准差：{player_height_std}')
# 对身高进行排序，选取前25%
data_sorted_height = processed_data.sort_values(by='身高/cm')
name = []
name.append('姓名')
sort_num = math.floor(len(data_sorted_height) * 0.25)
for i in range(sort_num):
    name.append(data_sorted_height['中文名'].array[i])
with open('女篮身高前25%名单.txt', 'w') as file:
    file.write('\n'.join(name))
plt.rcParams['font.family']=['SimHei']
plt.subplot(1,2,1)
plt.title('女篮身高箱型图')
plt.boxplot(processed_data['身高/cm'].array)
plt.subplot(1,2,2)
plt.title('女篮身高直方图')
plt.hist(processed_data['身高/cm'].array)
plt.show()
plt.savefig('女篮身高数据.png')